using System.Data;
using System.Globalization;
using Dapper;
using MediatR;
using Microsoft.Extensions.Logging;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using RBAC.ErrorCode;
using RBAC.Interstructrue.Base;
using RBAC.Write.Api.Application.Command.ERP.PurchasingManagement.RawMaterialSuppliers;

namespace RBAC.Write.Api.Application.Handler.ERP.PurchasingManagement.RawMaterialSuppliers
{
    /// <summary>
    /// 导入供应商信息处理
    /// </summary>
    public class ImportRawMaterialSuppliersCommandHandlers : IRequestHandler<ImportRawMaterialSuppliersCommand, APIResult<int>>
    {
        private readonly IDbConnectionFactory _connectionFactory;
        private readonly ILogger<ImportRawMaterialSuppliersCommandHandlers> _logger;

        public ImportRawMaterialSuppliersCommandHandlers(IDbConnectionFactory connectionFactory, ILogger<ImportRawMaterialSuppliersCommandHandlers> logger)
        {
            _connectionFactory = connectionFactory;
            _logger = logger;
        }

        public async Task<APIResult<int>> Handle(ImportRawMaterialSuppliersCommand request, CancellationToken cancellationToken)
        {
            var res = new APIResult<int>();
            if (request.FileBytes == null || request.FileBytes.Length == 0)
            {
                res.Code = APIEnums.Fail;
                res.Msg = "文件内容为空";
                return res;
            }

            try
            {
                IWorkbook workbook = CreateWorkbook(request.FileName, request.FileBytes);
                var sheet = workbook.GetSheetAt(0);
                if (sheet == null)
                {
                    res.Code = APIEnums.Fail;
                    res.Msg = "Excel中未找到工作表";
                    return res;
                }

                int startRow = sheet.FirstRowNum;
                if (request.HasHeader) startRow += 1;

                int success = 0;
                int processed = 0;
                int failed = 0;
                var errorSamples = new List<string>();
                using var conn = _connectionFactory.CreateConnectionWithRetry();
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                for (int i = startRow; i <= sheet.LastRowNum; i++)
                {
                    var row = sheet.GetRow(i);
                    if (row == null) continue; // 跳过空行

                    try
                    {
                        processed++;
                        // 按导出顺序读取
                        // 现规则：不导入供应商ID，首列为供应商名称
                        string supplierName = GetString(row, 0);
                        string supplierAlias = GetString(row, 1);
                        string shortName = GetString(row, 2);
                        string provinceCity = GetString(row, 3);
                        string supplyCategory = GetString(row, 4);
                        string supplierLevel = GetString(row, 5);
                        string supplyProducts = GetString(row, 6);
                        string companyAddress = GetString(row, 7);
                        string postalCode = GetString(row, 8);
                        string phone = GetString(row, 9);
                        string remarks = GetString(row, 10);
                        int? status = GetNullableInt(row, 11);
                        string businessContactName = GetString(row, 12);
                        string businessMobile = GetString(row, 13);
                        string financeContactName = GetString(row, 14);
                        string financeMobile = GetString(row, 15);
                        string bankName = GetString(row, 16);
                        string bankAddress = GetString(row, 17);
                        string accountNumber = GetString(row, 18);
                        string taxNumber = GetString(row, 19);

                        using var tran = conn.BeginTransaction();
                        try
                        {
                            // 不导入供应商ID：一律插入新供应商
                            int changed = 0;
                            long supplierId = await InsertSupplier(conn, tran, cancellationToken, supplierName, supplierAlias, shortName, provinceCity,
                                supplyCategory, supplierLevel, supplyProducts, companyAddress, postalCode, phone, remarks, status ?? 0);
                            changed++;

                            // 业务联系人 upsert（取第一条）
                            if (!string.IsNullOrWhiteSpace(businessContactName) || !string.IsNullOrWhiteSpace(businessMobile))
                            {
                                var businessId = await conn.QueryFirstOrDefaultAsync<long?>(new CommandDefinition(
                                    "SELECT Id FROM SupplierBusiness WHERE Supplier_Id=@Supplier_Id AND IsDel=0 LIMIT 1",
                                    new { Supplier_Id = supplierId }, tran, cancellationToken: cancellationToken));
                                if (businessId.HasValue)
                                {
                                    changed += await conn.ExecuteAsync(new CommandDefinition(
                                        "UPDATE SupplierBusiness SET Contact_Name=@Contact_Name, Mobile_Phone=@Mobile_Phone WHERE Id=@Id",
                                        new { Id = businessId.Value, Contact_Name = businessContactName, Mobile_Phone = businessMobile }, tran, cancellationToken: cancellationToken));
                                }
                                else
                                {
                                    changed += await conn.ExecuteAsync(new CommandDefinition(
                                        "INSERT INTO SupplierBusiness (Supplier_Id, Contact_Name, Mobile_Phone, IsDel) VALUES (@Supplier_Id, @Contact_Name, @Mobile_Phone, 0)",
                                        new { Supplier_Id = supplierId, Contact_Name = businessContactName, Mobile_Phone = businessMobile }, tran, cancellationToken: cancellationToken));
                                }
                            }

                            // 财务联系人 upsert（取第一条）
                            if (!string.IsNullOrWhiteSpace(financeContactName) || !string.IsNullOrWhiteSpace(financeMobile))
                            {
                                var financeId = await conn.QueryFirstOrDefaultAsync<long?>(new CommandDefinition(
                                    "SELECT Id FROM SupplierFinance WHERE Supplier_Id=@Supplier_Id AND IsDel=0 LIMIT 1",
                                    new { Supplier_Id = supplierId }, tran, cancellationToken: cancellationToken));
                                if (financeId.HasValue)
                                {
                                    changed += await conn.ExecuteAsync(new CommandDefinition(
                                        "UPDATE SupplierFinance SET Contact_Name=@Contact_Name, Mobile_Phone=@Mobile_Phone WHERE Id=@Id",
                                        new { Id = financeId.Value, Contact_Name = financeContactName, Mobile_Phone = financeMobile }, tran, cancellationToken: cancellationToken));
                                }
                                else
                                {
                                    changed += await conn.ExecuteAsync(new CommandDefinition(
                                        "INSERT INTO SupplierFinance (Supplier_Id, Contact_Name, Mobile_Phone, IsDel) VALUES (@Supplier_Id, @Contact_Name, @Mobile_Phone, 0)",
                                        new { Supplier_Id = supplierId, Contact_Name = financeContactName, Mobile_Phone = financeMobile }, tran, cancellationToken: cancellationToken));
                                }
                            }

                            // 开票信息 upsert（取第一条）
                            if (!string.IsNullOrWhiteSpace(bankName) || !string.IsNullOrWhiteSpace(bankAddress) || !string.IsNullOrWhiteSpace(accountNumber) || !string.IsNullOrWhiteSpace(taxNumber))
                            {
                                var invoiceId = await conn.QueryFirstOrDefaultAsync<long?>(new CommandDefinition(
                                    "SELECT Id FROM SupplierInvoice WHERE Supplier_Id=@Supplier_Id AND IsDel=0 LIMIT 1",
                                    new { Supplier_Id = supplierId }, tran, cancellationToken: cancellationToken));
                                if (invoiceId.HasValue)
                                {
                                    changed += await conn.ExecuteAsync(new CommandDefinition(
                                        @"UPDATE SupplierInvoice SET Bank_Name=@Bank_Name, Bank_Address=@Bank_Address, Account_Number=@Account_Number, Tax_Number=@Tax_Number WHERE Id=@Id",
                                        new { Id = invoiceId.Value, Bank_Name = bankName, Bank_Address = bankAddress, Account_Number = accountNumber, Tax_Number = taxNumber }, tran, cancellationToken: cancellationToken));
                                }
                                else
                                {
                                    changed += await conn.ExecuteAsync(new CommandDefinition(
                                        @"INSERT INTO SupplierInvoice (Supplier_Id, Bank_Name, Bank_Address, Account_Number, Tax_Number, IsDel) VALUES (@Supplier_Id, @Bank_Name, @Bank_Address, @Account_Number, @Tax_Number, 0)",
                                        new { Supplier_Id = supplierId, Bank_Name = bankName, Bank_Address = bankAddress, Account_Number = accountNumber, Tax_Number = taxNumber }, tran, cancellationToken: cancellationToken));
                                }
                            }

                            tran.Commit();
                            // 验证是否落库
                            var verify = await conn.ExecuteScalarAsync<int>(new CommandDefinition(
                                "SELECT COUNT(1) FROM Supplier WHERE Id=@Id AND IsDeleted=0", new { Id = supplierId }, cancellationToken: cancellationToken));
                            if (verify > 0 && changed > 0)
                            {
                                success++;
                            }
                            else
                            {
                                _logger.LogWarning("导入未变更或未写入：Row={RowIndex}, SupplierId={SupplierId}, Changed={Changed}", i + 1, supplierId, changed);
                            }
                        }
                        catch (Exception exRow)
                        {
                            try { tran.Rollback(); } catch { }
                            failed++;
                            var msg = $"第{i + 1}行导入失败: {exRow.Message}";
                            if (errorSamples.Count < 5) errorSamples.Add(msg);
                            _logger.LogError(exRow, "{Msg}", msg);
                        }
                    }
                    catch (Exception ex)
                    {
                        failed++;
                        var msg = $"第{i + 1}行解析失败: {ex.Message}";
                        if (errorSamples.Count < 5) errorSamples.Add(msg);
                        _logger.LogError(ex, "{Msg}", msg);
                    }
                }

                res.Code = APIEnums.Success;
                res.Msg = $"导入完成：总行数 {processed}，成功 {success}，失败 {failed}" + (errorSamples.Count > 0 ? $"；示例错误：{string.Join(" | ", errorSamples)}" : string.Empty);
                res.Data = success;
                return res;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "导入失败");
                return new APIResult<int>
                {
                    Code = APIEnums.Error,
                    Msg = $"导入失败: {ex.Message}",
                    Data = 0
                };
            }
        }

        private static IWorkbook CreateWorkbook(string fileName, byte[] bytes)
        {
            // 不释放 MemoryStream，避免某些情况下 Workbook 仍需访问流
            var ms = new MemoryStream(bytes);

            // 先尝试通过魔数判断：
            // XLSX/OOXML -> ZIP 头：50 4B 03 04
            // XLS/OLE2  -> D0 CF 11 E0
            try
            {
                var sig = new byte[Math.Min(4, bytes.Length)];
                ms.Read(sig, 0, sig.Length);
                ms.Position = 0;
                bool isZip = sig.Length >= 4 && sig[0] == 0x50 && sig[1] == 0x4B && sig[2] == 0x03 && sig[3] == 0x04;
                bool isOle2 = sig.Length >= 4 && sig[0] == 0xD0 && sig[1] == 0xCF && sig[2] == 0x11 && sig[3] == 0xE0;

                bool extXlsx = !string.IsNullOrWhiteSpace(fileName) &&
                               (fileName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase) ||
                                fileName.EndsWith(".xlsm", StringComparison.OrdinalIgnoreCase));

                if (isZip || (extXlsx && !isOle2))
                {
                    // 2007+ xlsx/xlsm
                    return new XSSFWorkbook(ms);
                }
                if (isOle2 || (!extXlsx && !isZip))
                {
                    // 97-2003 xls
                    return new HSSFWorkbook(ms);
                }
            }
            catch
            {
                ms.Position = 0;
            }

            // 兜底：根据扩展名再尝试
            if (!string.IsNullOrWhiteSpace(fileName) &&
                (fileName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase) ||
                 fileName.EndsWith(".xlsm", StringComparison.OrdinalIgnoreCase)))
            {
                try { return new XSSFWorkbook(ms); } catch { ms.Position = 0; }
            }

            try { return new HSSFWorkbook(ms); } catch { ms.Position = 0; return new XSSFWorkbook(ms); }
        }

        private static string GetString(IRow row, int index)
        {
            var cell = row.GetCell(index);
            return cell?.ToString()?.Trim() ?? string.Empty;
        }

        private static int? GetNullableInt(IRow row, int index)
        {
            var s = GetString(row, index);
            if (int.TryParse(s, out var v)) return v;
            return null;
        }

        private static long GetLong(IRow row, int index)
        {
            var s = GetString(row, index);
            if (long.TryParse(s, out var v)) return v;
            return 0L;
        }

        private static async Task<long> InsertSupplier(IDbConnection conn, IDbTransaction tran, CancellationToken ct,
            string supplierName, string supplierAlias, string shortName, string provinceCity,
            string supplyCategory, string supplierLevel, string supplyProducts, string companyAddress,
            string postalCode, string phone, string remarks, int status)
        {
            var sql = @"INSERT INTO Supplier
                        (Supplier_Name, Supplier_Alias, Short_Name, Province_City, Supply_Category, Supplier_Level, Supply_Products,
                         Company_Address, Postal_Code, Phone, Remarks, Status, CreateTime, CreateUser, IsDeleted)
                        VALUES
                        (@Supplier_Name, @Supplier_Alias, @Short_Name, @Province_City, @Supply_Category, @Supplier_Level, @Supply_Products,
                         @Company_Address, @Postal_Code, @Phone, @Remarks, @Status, NOW(), 'import', 0);
                        SELECT LAST_INSERT_ID();";
            var id = await conn.ExecuteScalarAsync<long>(new CommandDefinition(sql, new
            {
                Supplier_Name = supplierName,
                Supplier_Alias = supplierAlias,
                Short_Name = shortName,
                Province_City = provinceCity,
                Supply_Category = supplyCategory,
                Supplier_Level = supplierLevel,
                Supply_Products = supplyProducts,
                Company_Address = companyAddress,
                Postal_Code = postalCode,
                Phone = phone,
                Remarks = remarks,
                Status = status
            }, tran, cancellationToken: ct));
            return id;
        }

        private static Task<int> UpdateSupplier(IDbConnection conn, IDbTransaction tran, CancellationToken ct, long id,
            string supplierName, string supplierAlias, string shortName, string provinceCity,
            string supplyCategory, string supplierLevel, string supplyProducts, string companyAddress,
            string postalCode, string phone, string remarks, int? status)
        {
            var sql = @"UPDATE Supplier SET
                        Supplier_Name=@Supplier_Name,
                        Supplier_Alias=@Supplier_Alias,
                        Short_Name=@Short_Name,
                        Province_City=@Province_City,
                        Supply_Category=@Supply_Category,
                        Supplier_Level=@Supplier_Level,
                        Supply_Products=@Supply_Products,
                        Company_Address=@Company_Address,
                        Postal_Code=@Postal_Code,
                        Phone=@Phone,
                        Remarks=@Remarks,
                        Status=COALESCE(@Status, Status)
                    WHERE Id=@Id AND IsDeleted=0";
            return conn.ExecuteAsync(new CommandDefinition(sql, new
            {
                Id = id,
                Supplier_Name = supplierName,
                Supplier_Alias = supplierAlias,
                Short_Name = shortName,
                Province_City = provinceCity,
                Supply_Category = supplyCategory,
                Supplier_Level = supplierLevel,
                Supply_Products = supplyProducts,
                Company_Address = companyAddress,
                Postal_Code = postalCode,
                Phone = phone,
                Remarks = remarks,
                Status = status
            }, tran, cancellationToken: ct));
        }
    }
}
